package my.dao.lmpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import my.bean.Cart;
import my.bean.Tea;
import my.bean.User;
import my.dao.ICartDao;
import my.dbUtil.DBUtils;
import oracle.net.aso.l;

public class ICartDaoImpl implements ICartDao {

	private Connection conn;

	public ICartDaoImpl() {
	}

	public ICartDaoImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public int addCart(Cart cart) throws SQLException {
		PreparedStatement prestmt = null;
		int record = 0;

		try {
			String sql = "insert into teacart values(cart_id.nextval,?,?,?,sysdate)";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, cart.getTea().getId());
			prestmt.setInt(2, cart.getUser().getId());
			prestmt.setDouble(3, cart.getCount());

			record = prestmt.executeUpdate();

			return record;
		} catch (SQLException e) {
			System.err.println("插入失败!");
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public int deleteCart(int id) throws SQLException {
		PreparedStatement prestmt = null;
		int record = 0;

		try {
			String sql = "delete from teacart where cart_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, id);

			record = prestmt.executeUpdate();

			return record;
		} catch (SQLException e) {
			System.err.println("删除失败!");
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public int modifyCart(Cart cart) throws SQLException {
		PreparedStatement prestmt = null;
		int record = 0;

		try {
			String sql = "update teacart set tea_id=?,user_id=?,count=?,add_date=sysdate where cart_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, cart.getTea().getId());
			prestmt.setInt(2, cart.getUser().getId());
			prestmt.setDouble(3, cart.getCount());
			prestmt.setInt(4, cart.getId());

			record = prestmt.executeUpdate();

			return record;
		} catch (SQLException e) {
			System.err.println("修改失败!");
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public List<Cart> findByTeaName(int id, String name) {
		List<Cart> list = new ArrayList<>();
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from teacart natural join tea natural join users where user_id=? and tea_name=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, id);
			prestmt.setString(2, "%" + name + "%");

			rs = prestmt.executeQuery();

			while (rs.next()) {
				id = rs.getInt("cart_id");

				int tid = rs.getInt("tea_id");
				String tName = rs.getString("tea_name");
				String from = rs.getString("tea_from");
				double price = rs.getDouble("tea_price");
				double stock = rs.getDouble("tea_stock");
				String introduction = rs.getString("tea_introduction");
				int type = rs.getInt("tea_type");
				Tea tea = new Tea(tid, tName, from, price, stock, type, introduction);

				int uid = rs.getInt("user_id");
				String account = rs.getString("user_account");
				String password = rs.getString("user_password").trim();
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("user_regdate");
				User user = new User(uid, account, password, email, phone, regDate);

				double count = rs.getDouble("count");
				Timestamp addDate = rs.getTimestamp("add_date");

				Cart cart = new Cart(id, user, tea, count, addDate);
				list.add(cart);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public List<Cart> findByTeaId(int uid, int tid) {
		List<Cart> list = new ArrayList<>();
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from teacart natural join tea natural join users where user_id=? and tea_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, uid);
			prestmt.setInt(2, tid);

			rs = prestmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("cart_id");

				tid = rs.getInt("tea_id");
				String tName = rs.getString("tea_name");
				String from = rs.getString("tea_from");
				double price = rs.getDouble("tea_price");
				double stock = rs.getDouble("tea_stock");
				String introduction = rs.getString("tea_introduction");
				int type = rs.getInt("tea_type");
				Tea tea = new Tea(tid, tName, from, price, stock, type, introduction);

				uid = rs.getInt("user_id");
				String account = rs.getString("user_account");
				String password = rs.getString("user_password").trim();
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("user_regdate");
				User user = new User(uid, account, password, email, phone, regDate);

				double count = rs.getDouble("count");
				Timestamp addDate = rs.getTimestamp("add_date");

				Cart cart = new Cart(id, user, tea, count, addDate);
				list.add(cart);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public List<Cart> findByUserid(int id) {
		List<Cart> list = new ArrayList<>();
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from teacart natural join tea natural join users where user_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, id);

			rs = prestmt.executeQuery();

			while (rs.next()) {
				id = rs.getInt("cart_id");

				int tid = rs.getInt("tea_id");
				String tName = rs.getString("tea_name");
				String from = rs.getString("tea_from");
				double price = rs.getDouble("tea_price");
				double stock = rs.getDouble("tea_stock");
				String introduction = rs.getString("tea_introduction");
				int type = rs.getInt("tea_type");
				Tea tea = new Tea(tid, tName, from, price, stock, type, introduction);

				int uid = rs.getInt("user_id");
				String account = rs.getString("user_account");
				String password = rs.getString("user_password").trim();
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("user_regdate");
				User user = new User(uid, account, password, email, phone, regDate);

				double count = rs.getDouble("count");
				Timestamp addDate = rs.getTimestamp("add_date");

				Cart cart = new Cart(id, user, tea, count, addDate);
				list.add(cart);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public Cart findByCartId(int id) {
		Cart cart = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from teacart natural join tea natural join users where cart_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, id);

			rs = prestmt.executeQuery();

			while (rs.next()) {
				id = rs.getInt("cart_id");

				int tid = rs.getInt("tea_id");
				String tName = rs.getString("tea_name");
				String from = rs.getString("tea_from");
				double price = rs.getDouble("tea_price");
				double stock = rs.getDouble("tea_stock");
				String introduction = rs.getString("tea_introduction");
				int type = rs.getInt("tea_type");
				Tea tea = new Tea(tid, tName, from, price, stock, type, introduction);

				int uid = rs.getInt("user_id");
				String account = rs.getString("user_account");
				String password = rs.getString("user_password").trim();
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("user_regdate");
				User user = new User(uid, account, password, email, phone, regDate);

				double count = rs.getDouble("count");
				Timestamp addDate = rs.getTimestamp("add_date");

				cart = new Cart(id, user, tea, count, addDate);
			}
			return cart;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return cart;
	}

}
